﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using Utility;
namespace Service
{
    /// <summary>
    /// 功能：HW_SchoolMergeLog类数据访问
    /// 创建人：Wilson
    /// 创建时间：2013-1-30   
    /// </summary>
    public class HW_SchoolMergeLogService : IDisposable
    {
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~HW_SchoolMergeLogService()
        {
            Dispose(false);
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
		#region##MapperHW_SchoolMergeLog
        public IList<HW_SchoolMergeLog> MapperList(SqlDataReader reader)
         {
            IList<HW_SchoolMergeLog> list = new List<HW_SchoolMergeLog>();
            HW_SchoolMergeLog obj=null;
            while (reader.Read())
            {
                obj = new HW_SchoolMergeLog();
                obj.MergeLodId = (int)reader["MergeLodId"];
                obj.YearCode = reader["YearCode"] as string;
                obj.SchoolCode = (string)reader["SchoolCode"];
                obj.SchoolName = (string)reader["SchoolName"];
                obj.UploadStudentNumber = reader["UploadStudentNumber"] as int?;
                obj.NewSchoolCode = (string)reader["NewSchoolCode"];
                obj.NewSchoolName = (string)reader["NewSchoolName"];
                obj.Version = (int)reader["Version"];
                list.Add(obj);
            }
            return list;
         }
        public HW_SchoolMergeLog Mapper(SqlDataReader reader)
         {
            HW_SchoolMergeLog obj=null;
            if (reader.Read())
            {
                obj = new HW_SchoolMergeLog();
                obj.MergeLodId = (int)reader["MergeLodId"];
                obj.YearCode = reader["YearCode"] as string;
                obj.SchoolCode = (string)reader["SchoolCode"];
                obj.SchoolName = (string)reader["SchoolName"];
                    obj.UploadStudentNumber = reader["UploadStudentNumber"] as int?;
                obj.NewSchoolCode = (string)reader["NewSchoolCode"];
                obj.NewSchoolName = (string)reader["NewSchoolName"];
                obj.Version = (int)reader["Version"];
            }
            return obj;
         }
        #endregion
        #region##通过主键ID得到HW_SchoolMergeLog
        /// <summary>
        /// 功能：通过ID得到HW_SchoolMergeLog
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public HW_SchoolMergeLog GetById(int mergelodid)
        {            
            string sql = "select * from HW_SchoolMergeLog where MergeLodId = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",4)
            };
            
            SqlDataReader reader=SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql,sp);
            return Mapper(reader);
        }
        public IList<HW_SchoolMergeLog> GetByNewSchoolId(string schoolId)
        {
            string sql = "select * from HW_SchoolMergeLog where NewSchoolCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",schoolId)
            };

            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            return MapperList(reader);
        }
        public IList<HW_SchoolMergeLog> GetByOldSchoolId(string schoolId)
        {
            string sql = "select * from HW_SchoolMergeLog where SchoolCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",schoolId)
            };

            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            return MapperList(reader);
        }
        #endregion
		
		#region##通过主键ID删除HW_SchoolMergeLog
        /// <summary>
        /// 功能：通过主键ID删除HW_SchoolMergeLog
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(int mergelodid)
		{
			int i = 0;
			string sql = "delete from HW_SchoolMergeLog where MergeLodId = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",4)
            };
            i= SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
			return i;
		}
        /// <summary>
        /// 功能：通过主键ID删除HW_SchoolMergeLog
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(SqlTransaction transaction,int mergelodid)
		{
			int i = 0;
			string sql = "delete from HW_SchoolMergeLog where MergeLodId = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",4)
            };
            i= SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
			return i;
		}
		#endregion			
		
        #region##添加HW_SchoolMergeLog
        /// <summary>
        /// 功能：添加HW_SchoolMergeLog
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(HW_SchoolMergeLog hw_schoolmergelog)
        {
            int result = 0;
            string sql = "insert into HW_SchoolMergeLog(MergeLodId,YearCode,SchoolCode,SchoolName,UploadStudentNumber,NewSchoolCode,NewSchoolName,Version) values(@MergeLodId,@YearCode,@SchoolCode,@SchoolName,@UploadStudentNumber,@NewSchoolCode,@NewSchoolName,@Version)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@MergeLodId",hw_schoolmergelog.MergeLodId),
				new SqlParameter("@YearCode",hw_schoolmergelog.YearCode),
				new SqlParameter("@SchoolCode",hw_schoolmergelog.SchoolCode),
				new SqlParameter("@SchoolName",hw_schoolmergelog.SchoolName),
				new SqlParameter("@UploadStudentNumber",hw_schoolmergelog.UploadStudentNumber),
				new SqlParameter("@NewSchoolCode",hw_schoolmergelog.NewSchoolCode),
				new SqlParameter("@NewSchoolName",hw_schoolmergelog.NewSchoolName),
				new SqlParameter("@Version",hw_schoolmergelog.Version)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：添加HW_SchoolMergeLog
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(SqlTransaction transaction,HW_SchoolMergeLog hw_schoolmergelog)
        {
            int result = 0;
            string sql = "insert into HW_SchoolMergeLog(YearCode,SchoolCode,SchoolName,UploadStudentNumber,NewSchoolCode,NewSchoolName,Version,MergeDate) values(@YearCode,@SchoolCode,@SchoolName,@UploadStudentNumber,@NewSchoolCode,@NewSchoolName,@Version,@MergeDate)";			
            SqlParameter[] sp = new SqlParameter[]
            {
            //    new SqlParameter("@MergeLodId",hw_schoolmergelog.MergeLodId),
				new SqlParameter("@YearCode",hw_schoolmergelog.YearCode),
				new SqlParameter("@SchoolCode",hw_schoolmergelog.SchoolCode),
				new SqlParameter("@SchoolName",hw_schoolmergelog.SchoolName),
				new SqlParameter("@UploadStudentNumber",hw_schoolmergelog.UploadStudentNumber),
				new SqlParameter("@NewSchoolCode",hw_schoolmergelog.NewSchoolCode),
				new SqlParameter("@NewSchoolName",hw_schoolmergelog.NewSchoolName),
				new SqlParameter("@Version",hw_schoolmergelog.Version),
                new SqlParameter("@MergeDate",hw_schoolmergelog.MergeDate)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##得到HW_SchoolMergeLog集合
		/// <summary>
        /// 功能：得到HW_SchoolMergeLog集合
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>       
        /// <returns></returns>
		public IList<HW_SchoolMergeLog> GetList()
		{
			string sql = "select * from HW_SchoolMergeLog";			
			SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql);
            return MapperList(reader);
		}
		#endregion
		
		#region##修改HW_SchoolMergeLog
        /// <summary>
        /// 功能：修改HW_SchoolMergeLog
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_SchoolMergeLog(HW_SchoolMergeLog hw_schoolmergelog)
        {
            int result = 0;
            string sql = "update HW_SchoolMergeLog set MergeLodId = @MergeLodId,YearCode = @YearCode,SchoolCode = @SchoolCode,SchoolName = @SchoolName,UploadStudentNumber = @UploadStudentNumber,NewSchoolCode = @NewSchoolCode,NewSchoolName = @NewSchoolName,Version = @Version where MergeLodId = @MergeLodId";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@MergeLodId",hw_schoolmergelog.MergeLodId),
				new SqlParameter("@YearCode",hw_schoolmergelog.YearCode),
				new SqlParameter("@SchoolCode",hw_schoolmergelog.SchoolCode),
				new SqlParameter("@SchoolName",hw_schoolmergelog.SchoolName),
				new SqlParameter("@UploadStudentNumber",hw_schoolmergelog.UploadStudentNumber),
				new SqlParameter("@NewSchoolCode",hw_schoolmergelog.NewSchoolCode),
				new SqlParameter("@NewSchoolName",hw_schoolmergelog.NewSchoolName),
				new SqlParameter("@Version",hw_schoolmergelog.Version)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：修改HW_SchoolMergeLog
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_SchoolMergeLog(SqlTransaction transaction,HW_SchoolMergeLog hw_schoolmergelog)
        {
            int result = 0;
            string sql = "update HW_SchoolMergeLog set MergeLodId = @MergeLodId,YearCode = @YearCode,SchoolCode = @SchoolCode,SchoolName = @SchoolName,UploadStudentNumber = @UploadStudentNumber,NewSchoolCode = @NewSchoolCode,NewSchoolName = @NewSchoolName,Version = @Version where MergeLodId = @MergeLodId";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@MergeLodId",hw_schoolmergelog.MergeLodId),
				new SqlParameter("@YearCode",hw_schoolmergelog.YearCode),
				new SqlParameter("@SchoolCode",hw_schoolmergelog.SchoolCode),
				new SqlParameter("@SchoolName",hw_schoolmergelog.SchoolName),
				new SqlParameter("@UploadStudentNumber",hw_schoolmergelog.UploadStudentNumber),
				new SqlParameter("@NewSchoolCode",hw_schoolmergelog.NewSchoolCode),
				new SqlParameter("@NewSchoolName",hw_schoolmergelog.NewSchoolName),
				new SqlParameter("@Version",hw_schoolmergelog.Version)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##统计HW_SchoolMergeLog
		/// <summary>
        /// 修改HW_SchoolMergeLog
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
		public int SelectCount()
		{
			int i = 0;
            string sql = "select count(*) from HW_SchoolMergeLog";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
		}
		/// <summary>
        /// 修改HW_SchoolMergeLog
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int SelectCountById()
        {
            int i=0;
            string sql="select count(MergeLodId) from HW_SchoolMergeLog";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
        }
		#endregion
        #region##自定义HW_SchoolMergeLog
//        public DataTable GetLogByNewSchool(string schoolid)
//        {
//            int i = 0;
////            string sql = @"WITH m(ShoolCode,NewShoolCode) AS(
////	SELECT R.SchoolCode,R.NewSchoolCode FROM dbo.HW_SchoolMergeLog R WHERE NewSchoolCode=@schoolid
////	UNION ALL SELECT child.SchoolCode,child.NewSchoolCode FROM m parent, HW_SchoolMergeLog child WHERE parent.ShoolCode=child.NewSchoolCode
////)
////select * from m";
////            SqlParameter[] sp = new SqlParameter[]
////            {
////                new SqlParameter("@schoolid",schoolid)
////            };
////            DataTable dt= SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
//            return i;
//        }
        public DataTable Search(int pageIndex, int pageSize, out int count, string SearchString)
        {
            string sql = @"select NewSchoolCode,NewSchoolName,Count(SchoolCode) SchoolNum,MergeDate from HW_SchoolMergeLog where (NewSchoolName like @SearchString) or SchoolName like @SearchString Group by NewSchoolCode,NewSchoolName,MergeDate order by MergeDate desc";
            SqlParameter[] sp=new  SqlParameter[]{
                new SqlParameter("@SearchString","%"+SearchString+"%"),
            };
            string sql2 = "select count(NewSchoolCode) from HW_SchoolMergeLog where (NewSchoolName like @SearchString) or SchoolName like @SearchString Group by NewSchoolCode,NewSchoolName,MergeDate";
            DataTable dt= SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            object o = SqlHelper.ExecuteScalar(Utility.WebConn.connString, CommandType.Text, sql2, sp);
            count = Convert.ToInt32(o);
            return dt;
        }
        public DataTable GetList(int pageIndex, int pageSize, out int count)
        {
            string sql = @"select NewSchoolCode,NewSchoolName,Count(SchoolCode) SchoolNum,MergeDate from HW_SchoolMergeLog Group by NewSchoolCode,NewSchoolName,MergeDate order by MergeDate desc";
            string sql2 = "select count(NewSchoolCode) from HW_SchoolMergeLog Group by NewSchoolCode,NewSchoolName,MergeDate";
            DataTable dt = SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, sql).Tables[0];
            object o = SqlHelper.ExecuteScalar(Utility.WebConn.connString, CommandType.Text, sql2);
            count = Convert.ToInt32(o);
            return dt;
        }
        public int DeleteByNewSchool(SqlTransaction trans, string schoolcode)
        {
            string sql = @"delete from HW_SchoolMergeLog where NewSchoolCode=@schoolcode";
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@schoolcode",schoolcode),
            };
            int result = SqlHelper.ExecuteNonQuery(Utility.WebConn.connString, CommandType.Text, sql, sp);

            return result;
        }
        public DataTable GetMergeSchool(string schoolcode)
        {
            string sql = @"WITH p(SchoolCode,NewShoolCode) AS(
	SELECT R.SchoolCode,R.NewSchoolCode FROM dbo.HW_SchoolMergeLog R WHERE NewSchoolCode=@school
)select SchoolCode from p";
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@school",schoolcode),
            };
            DataSet result = SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, sql, sp);

            return result.Tables[0];
        }
        #endregion
                
    }
}
